Load data from files:

library(tidyverse)
library(readxl)
library(plotly)

### Load Kynnetec AgroTrak pesticide data:
AgroTrak.CornSoy.dat <- read_excel("AgroTrak_CornSoy_v1.xlsx",
                          skip = 5) %>%
  select(Year, Crop,
         Type = "Pesticide Type",
         Active.Ingredient = "Active Ingredient",
         Volume.lbs = "AI volume\r\n(lb)",
         Base.Area.Treated = "Base Area Treated\r\n(acres)") %>%
  mutate(ai = str_replace_all(Active.Ingredient, " \\(.\\)", "")) 
glimpse(AgroTrak.CornSoy.dat)

### Concordance list:
srcTab <- read_csv("cclistInEcotox.csv")
glimpse(srcTab)
srcTab %>% filter(ai == "ABAMECTIN")

### Load honey bee toxicity values from ecotox export:
ecotox.dat0 <- 
  read_excel("ECOTOX-Terrestrial-Export_20211207_plusManualAdd.xlsx",
             sheet = "Terrestrial-Export") %>%
  rename(cas.number = "CAS Number", chemName = "Chemical Name") 
### Pare down ecotox values to those used in the model, and join with
### AgroTrak concordance list:
### (leaving full ecotox.dat0 just in case needed later)
ecotox.dat <- ecotox.dat0 %>%
  select(source,
         cas.number, 
         ConcType = `Conc 1 Type (Author)`,
         Effect, 
         LifeStage = `Organism Lifestage`,
         ExposureType = `Exposure Type`, 
         Endpoint, 
         ObservedDuration.d = `Observed Duration (Days)`,
         ObsRespMean = `Observed Response Mean`,
         ToxUnit = `Observed Response Units`) %>%
  filter(Endpoint %in% c("LD50", "NOEL")) %>%
  right_join(srcTab) %>%
  select(-inEcoTox, -inManualAdd, -CompTox.PREFERRED_NAME)
glimpse(ecotox.dat)  

Create a ranked list of pesticides by volume applied in AgroTrak:

AgroTrak.Ranked <- AgroTrak.CornSoy.dat %>%
  group_by(Type, ai) %>%
  summarize(Volume.lbs = round(sum(Volume.lbs, na.rm = TRUE)),
            Base.Area.Treated.sum = round(sum(Base.Area.Treated, 
                                              na.rm = TRUE)),
            nYears = length(unique(Year)),
            Base.Area.perYear = round(Base.Area.Treated.sum / nYears),
            Volume.perYear = round(Volume.lbs / nYears)) %>%
  arrange(Type, -Volume.lbs)

Adult Contact LD50

AdultContactLD50 <- ecotox.dat %>%
  filter(LifeStage == "Adult" &
           ExposureType == "Dermal" & 
           Endpoint == "LD50") %>%
  group_by(ai) %>%
  mutate(checkAI = any(grepl("AI|ai|ae", ToxUnit)),
         hasAI = grepl("AI|ai|ae", ToxUnit)) %>%
  group_by(ai, hasAI) %>%
  mutate(min.d.grp = min(abs(2 - ObservedDuration.d) + 2, 
                         na.rm = TRUE)) %>%
  filter(case_when(checkAI == TRUE ~ 
                     hasAI == TRUE & ObservedDuration.d <= min.d.grp + 2,
                   checkAI == FALSE ~
                     ObservedDuration.d <= min.d.grp +2 )) %>%
  ungroup() %>%
  select(-checkAI, -hasAI, -min.d.grp) %>%
  group_by(ai) %>%
  slice(which.min(ObsRespMean))
glimpse(AdultContactLD50)
unique(AdultContactLD50$ConcType)
unique(AdultContactLD50$ToxUnit) 
AgroTrak.AdultContactLD50.Rank <- left_join(AgroTrak.Ranked, AdultContactLD50)
write_csv(AgroTrak.AdultContactLD50.Rank,
          "Draft_AdultContactLD50.csv")

AdultContactLD50.summary <- AgroTrak.AdultContactLD50.Rank %>%
  group_by(Type) %>%
  mutate(totalVol = sum(Volume.lbs),
         pctVol = Volume.lbs / totalVol * 100,
         toxVal = !is.na(ObsRespMean)) %>%
  #select(Type, ai, Volume.lbs, totalVol, pctVol, toxVal)
  group_by(Type, toxVal) %>%
  summarize(toxPct = sum(pctVol)) %>%
  pivot_wider(names_from = toxVal, 
              values_from = toxPct) %>%
  rename("missing Tox value" = `FALSE`,
         "Tox value present" = `TRUE`)
AdultContactLD50.summary
## # A tibble: 3 x 3
## # Groups:   Type [3]
##   Type        `missing Tox value` `Tox value present`
##   <chr>                     <dbl>               <dbl>
## 1 Fungicide                  6.04                94.0
## 2 Herbicide                  3.43                96.6
## 3 Insecticide                8.28                91.7

Adult Oral LD50

AdultOralLD50 <- ecotox.dat %>%
  filter(LifeStage == "Adult" &
         ExposureType == "Food" & 
         Endpoint == "LD50") %>%
  group_by(ai) %>%
  mutate(checkAI = any(grepl("AI|ai|ae", ToxUnit)),
         hasAI = grepl("AI|ai|ae", ToxUnit)) %>%
  group_by(ai, hasAI) %>%
  mutate(min.d.grp = min(abs(2 - ObservedDuration.d) + 2, 
                         na.rm = TRUE)) %>%
  filter(case_when(checkAI == TRUE ~ 
                     hasAI == TRUE & ObservedDuration.d == min.d.grp,
                   checkAI == FALSE ~
                     ObservedDuration.d == min.d.grp)) %>%
      ungroup() %>%
      select(-checkAI, -hasAI, -min.d.grp) %>%
  group_by(ai) %>%
  slice(which.min(ObsRespMean))
glimpse(AdultOralLD50)
unique(AdultOralLD50$ConcType)
unique(AdultOralLD50$ToxUnit) 
### ^^ There are some 'ug/bee without AI in this one.
AgroTrak.AdultOralLD50.Rank <- left_join(AgroTrak.Ranked, AdultOralLD50)
write_csv(AgroTrak.AdultOralLD50.Rank,
          "Draft_AdultOralLD50.csv")

AdultOralLD50.summary <- AgroTrak.AdultOralLD50.Rank %>%
  group_by(Type) %>%
  mutate(totalVol = sum(Volume.lbs),
         pctVol = Volume.lbs / totalVol * 100,
         toxVal = !is.na(ObsRespMean)) %>%
  #select(Type, ai, Volume.lbs, totalVol, pctVol, toxVal)
  group_by(Type, toxVal) %>%
  summarize(toxPct = sum(pctVol)) %>%
  pivot_wider(names_from = toxVal, 
              values_from = toxPct) %>%
  rename("missing Tox value" = `FALSE`,
         "Tox value present" = `TRUE`)
AdultOralLD50.summary
## # A tibble: 3 x 3
## # Groups:   Type [3]
##   Type        `missing Tox value` `Tox value present`
##   <chr>                     <dbl>               <dbl>
## 1 Fungicide                  26.0                74.0
## 2 Herbicide                  30.5                69.5
## 3 Insecticide                77.2                22.8

Adult Oral NOEL

AdultOralNOEL <- ecotox.dat %>%
  filter(LifeStage == "Adult" &
         ExposureType == "Food" & 
         Endpoint == "NOEL") %>%
  group_by(ai) %>%
  mutate(checkAI = any(grepl("AI|ai|ae", ToxUnit)),
         hasAI = grepl("AI|ai|ae", ToxUnit)) %>%
  group_by(ai, hasAI) %>%
  mutate(min.d.grp = min(abs(10 - ObservedDuration.d) + 10, 
                         na.rm = TRUE)) %>%
  filter(case_when(checkAI == TRUE ~ 
                     hasAI == TRUE & ObservedDuration.d == min.d.grp,
                   checkAI == FALSE ~
                     ObservedDuration.d == min.d.grp)) %>%
      ungroup() %>%
      select(-checkAI, -hasAI, -min.d.grp) %>%
  group_by(ai) %>%
  slice(which.min(ObsRespMean))
glimpse(AdultOralNOEL)
unique(AdultOralNOEL$ConcType)
unique(AdultOralNOEL$ToxUnit)
AgroTrak.AdultOralNOEL.Rank <- left_join(AgroTrak.Ranked, AdultOralNOEL)
write_csv(AgroTrak.AdultOralNOEL.Rank,
          "Draft_AdultOralNOEL.csv")

AdultOralNOEL.summary <- AgroTrak.AdultOralNOEL.Rank %>%
  group_by(Type) %>%
  mutate(totalVol = sum(Volume.lbs),
         pctVol = Volume.lbs / totalVol * 100,
         toxVal = !is.na(ObsRespMean)) %>%
  #select(Type, ai, Volume.lbs, totalVol, pctVol, toxVal)
  group_by(Type, toxVal) %>%
  summarize(toxPct = sum(pctVol)) %>%
  pivot_wider(names_from = toxVal, 
              values_from = toxPct) %>%
  rename("missing Tox value" = `FALSE`,
         "Tox value present" = `TRUE`)
AdultOralNOEL.summary
## # A tibble: 3 x 3
## # Groups:   Type [3]
##   Type        `missing Tox value` `Tox value present`
##   <chr>                     <dbl>               <dbl>
## 1 Fungicide                  54.0                46.0
## 2 Herbicide                  89.4                10.6
## 3 Insecticide                87.8                12.2

Larval Oral LD50

LarvaOralLD50 <- ecotox.dat %>%
  filter(LifeStage == "Larva" &
         ExposureType == "Food" & 
         Endpoint == "LD50") %>%
  group_by(ai) %>%
  mutate(checkAI = any(grepl("AI|ai|ae", ToxUnit)),
         hasAI = grepl("AI|ai|ae", ToxUnit)) %>%
  group_by(ai, hasAI) %>%
  mutate(min.d.grp = min(abs(3 - ObservedDuration.d) + 3, 
                         na.rm = TRUE)) %>%
  filter(case_when(checkAI == TRUE ~ 
                     hasAI == TRUE & ObservedDuration.d == min.d.grp,
                   checkAI == FALSE ~
                     ObservedDuration.d == min.d.grp)) %>%
      ungroup() %>%
      select(-checkAI, -hasAI, -min.d.grp) %>%
  group_by(ai) %>%
  slice(which.min(ObsRespMean))
glimpse(LarvaOralLD50)
unique(LarvaOralLD50$ConcType)
unique(LarvaOralLD50$ToxUnit)
AgroTrak.LarvaOralLD50.Rank <- left_join(AgroTrak.Ranked, LarvaOralLD50)
write_csv(AgroTrak.LarvaOralLD50.Rank,
          "Draft_LarvaOralLD50.csv")

LarvaOralLD50.summary <- AgroTrak.LarvaOralLD50.Rank %>%
  group_by(Type) %>%
  mutate(totalVol = sum(Volume.lbs),
         pctVol = Volume.lbs / totalVol * 100,
         toxVal = !is.na(ObsRespMean)) %>%
  #select(Type, ai, Volume.lbs, totalVol, pctVol, toxVal)
  group_by(Type, toxVal) %>%
  summarize(toxPct = sum(pctVol)) %>%
  pivot_wider(names_from = toxVal, 
              values_from = toxPct) %>%
  rename("missing Tox value" = `FALSE`,
         "Tox value present" = `TRUE`)
LarvaOralLD50.summary
## # A tibble: 3 x 3
## # Groups:   Type [3]
##   Type        `missing Tox value` `Tox value present`
##   <chr>                     <dbl>               <dbl>
## 1 Fungicide                  36.8                63.2
## 2 Herbicide                  73.4                26.6
## 3 Insecticide                86.7                13.3

Larval Oral NOEL

LarvaOralNOEL <- ecotox.dat %>%
  filter(LifeStage == "Larva" &
         ExposureType == "Food" & 
         Endpoint == "NOEL") %>%
  group_by(ai) %>%
  mutate(checkAI = any(grepl("AI|ai|ae", ToxUnit)),
         hasAI = grepl("AI|ai|ae", ToxUnit)) %>%
  group_by(ai, hasAI) %>%
  mutate(min.d.grp = min(abs(22 - ObservedDuration.d) + 22, 
                         na.rm = TRUE)) %>%
  filter(case_when(checkAI == TRUE ~ 
                     hasAI == TRUE & ObservedDuration.d == min.d.grp,
                   checkAI == FALSE ~
                     ObservedDuration.d == min.d.grp)) %>%
      ungroup() %>%
      select(-checkAI, -hasAI, -min.d.grp) %>%
  group_by(ai) %>%
  slice(which.min(ObsRespMean))
glimpse(LarvaOralNOEL)
unique(LarvaOralNOEL$ConcType)
unique(LarvaOralNOEL$ToxUnit)
AgroTrak.LarvaOralNOEL.Rank <- left_join(AgroTrak.Ranked, LarvaOralNOEL)
write_csv(AgroTrak.LarvaOralNOEL.Rank,
          "Draft_LarvaOralNOEL.csv")

LarvaOralNOEL.summary <- AgroTrak.LarvaOralNOEL.Rank %>%
  group_by(Type) %>%
  mutate(totalVol = sum(Volume.lbs),
         pctVol = Volume.lbs / totalVol * 100,
         toxVal = !is.na(ObsRespMean)) %>%
  #select(Type, ai, Volume.lbs, totalVol, pctVol, toxVal)
  group_by(Type, toxVal) %>%
  summarize(toxPct = sum(pctVol)) %>%
  pivot_wider(names_from = toxVal, 
              values_from = toxPct) %>%
  rename("missing Tox value" = `FALSE`,
         "Tox value present" = `TRUE`)
LarvaOralNOEL.summary
## # A tibble: 3 x 3
## # Groups:   Type [3]
##   Type        `missing Tox value` `Tox value present`
##   <chr>                     <dbl>               <dbl>
## 1 Fungicide                  39.1                60.9
## 2 Herbicide                  73.7                26.3
## 3 Insecticide                88.1                11.9

Analysis of Adult Contact LD50 RQs for Corn & Soybean

List of active ingredients that made up greater than 1% of a group’s volume in any single year but has no adult dermal LD50 value:

CornSoyAdultContact.dat %>%
  filter(is.na(RQ.pctOfType) & Volume.pctOfType > 1) %>%
  distinct(Type, ai) %>%
  arrange(Type)
## # A tibble: 17 x 2
##    Type        ai               
##    <chr>       <chr>            
##  1 Fungicide   SULFUR           
##  2 Fungicide   TEBUCONAZOLE     
##  3 Fungicide   COPPER SULFATE   
##  4 Fungicide   TETRACONAZOLE    
##  5 Fungicide   FLUSILAZOLE      
##  6 Fungicide   PETROLEUM OIL    
##  7 Fungicide   PHOSPHORIC ACID  
##  8 Herbicide   CYANAZINE        
##  9 Herbicide   ACIFLUORFEN      
## 10 Herbicide   BENTAZONE        
## 11 Herbicide   FOMESAFEN        
## 12 Herbicide   SULFOSATE        
## 13 Herbicide   GLUFOSINATE      
## 14 Herbicide   DIMETHENAMID-P   
## 15 Insecticide TEBUPIRIMPHOS    
## 16 Insecticide BIFENTHRIN       
## 17 Insecticide ZETA-CYPERMETHRIN

Figures

Total pesticide volume applied.


Pesticide volume applied per treated acre.


Summed honey bee risk quotient values for all pesticides.


Summed honey bee risk quotient values per treated acre.


Same RQ plots, faceted & scaled to see fungicide & herbicide trends.


Next steps:


Insecticides used since 2005:


Corn Insecticides:

Crop Type ai VolumeContrib RQContrib Diff.11
Corn Insecticide FIPRONIL 1.0 21.1 20.1
Corn Insecticide CHLORPYRIFOS 24.5 38.8 14.3
Corn Insecticide PERMETHRIN 1.4 11.9 10.5
Corn Insecticide CYHALOTHRIN-LAMBDA 1.6 9.3 7.7
Corn Insecticide DELTAMETHRIN 0.0 6.2 6.2
Corn Insecticide CYFLUTHRIN 0.8 5.3 4.5
Corn Insecticide CYHALOTHRIN-GAMMA 0.0 1.5 1.5
Corn Insecticide ESFENVALERATE 0.1 1.2 1.1
Corn Insecticide ALPHA-CYPERMETHRIN 0.1 0.9 0.8
Corn Insecticide CHLORETHOXYFOS 0.7 1.5 0.8
Corn Insecticide INDOXACARB 0.2 0.9 0.7
Corn Insecticide TEFLUTHRIN 8.2 8.7 0.5
Corn Insecticide DIMETHOATE 1.7 2.1 0.4
Corn Insecticide IMIDACLOPRID 0.1 0.5 0.4
Corn Insecticide ABAMECTIN 0.0 0.3 0.3
Corn Insecticide SPINETORAM 0.0 0.2 0.2
Corn Insecticide CYPERMETHRIN 0.1 0.2 0.1
Corn Insecticide ACETAMIPRID 0.0 0.0 0.0
Corn Insecticide AZADIRACHTIN 0.0 0.0 0.0
Corn Insecticide CYFLUMETOFEN 0.0 0.0 0.0
Corn Insecticide DISULFOTON 0.0 0.0 0.0
Corn Insecticide FLUPYRADIFURONE 0.0 0.0 0.0
Corn Insecticide MALATHION 0.2 0.2 0.0
Corn Insecticide METALDEHYDE 0.0 0.0 0.0
Corn Insecticide PIPERONYL BUTOXIDE 0.0 0.0 0.0
Corn Insecticide PYRETHRINS 0.0 0.0 0.0
Corn Insecticide ROTENONE 0.0 0.0 0.0
Corn Insecticide SPINOSYN 0.0 0.0 0.0
Corn Insecticide SULFOXAFLOR 0.0 0.0 0.0
Corn Insecticide CHLORANTRANILIPROLE 0.1 0.0 -0.1
Corn Insecticide DIAZINON 0.1 0.0 -0.1
Corn Insecticide FENPYROXIMATE 0.1 0.0 -0.1
Corn Insecticide FLUBENDIAMIDE 0.1 0.0 -0.1
Corn Insecticide METHOXYFENOZIDE 0.1 0.0 -0.1
Corn Insecticide METHOMYL 0.2 0.0 -0.2
Corn Insecticide CARBARYL 0.4 0.1 -0.3
Corn Insecticide ETOXAZOLE 0.4 0.0 -0.4
Corn Insecticide PHORATE 0.5 0.0 -0.5
Corn Insecticide METHYL PARATHION 1.3 0.7 -0.6
Corn Insecticide ETHOPROPHOS 0.9 0.1 -0.8
Corn Insecticide HEXYTHIAZOX 0.8 0.0 -0.8
Corn Insecticide ACEPHATE 1.7 0.3 -1.4
Corn Insecticide SPIROMESIFEN 1.4 0.0 -1.4
Corn Insecticide CARBOFURAN 5.5 4.0 -1.5
Corn Insecticide SULFUR 4.4 0.0 -4.4
Corn Insecticide TERBUFOS 8.7 0.4 -8.3
Corn Insecticide PROPARGITE 30.8 0.1 -30.7
Corn Insecticide BACILLUS THURINGIENSIS 0.2 NaN NaN
Corn Insecticide BIFENTHRIN 6.7 NaN NaN
Corn Insecticide HARPIN PROTEIN 0.0 NaN NaN
Corn Insecticide TEBUPIRIMPHOS 6.9 NaN NaN
Corn Insecticide ZETA-CYPERMETHRIN 0.3 NaN NaN

Soybean Insecticides:

Crop Type ai VolumeContrib RQContrib Diff.11
Soybeans Insecticide CYHALOTHRIN-LAMBDA 6.2 17.8 11.6
Soybeans Insecticide DELTAMETHRIN 0.1 6.0 5.9
Soybeans Insecticide ESFENVALERATE 1.0 5.8 4.8
Soybeans Insecticide CYFLUTHRIN 1.5 5.6 4.1
Soybeans Insecticide THIAMETHOXAM 0.7 4.4 3.7
Soybeans Insecticide CYHALOTHRIN-GAMMA 0.2 3.5 3.3
Soybeans Insecticide PERMETHRIN 0.8 3.5 2.7
Soybeans Insecticide SPINOSYN 0.0 1.7 1.7
Soybeans Insecticide ABAMECTIN 0.0 1.1 1.1
Soybeans Insecticide ALPHA-CYPERMETHRIN 0.2 1.0 0.8
Soybeans Insecticide IMIDACLOPRID 1.6 2.3 0.7
Soybeans Insecticide SPINETORAM 0.2 0.8 0.6
Soybeans Insecticide FIPRONIL 0.0 0.6 0.6
Soybeans Insecticide CLOTHIANIDIN 0.1 0.6 0.5
Soybeans Insecticide INDOXACARB 0.2 0.3 0.1
Soybeans Insecticide AFIDOPYROPEN 0.0 0.0 0.0
Soybeans Insecticide ETOXAZOLE 0.0 0.0 0.0
Soybeans Insecticide PIPERONYL BUTOXIDE 0.0 0.0 0.0
Soybeans Insecticide PYRETHRINS 0.0 0.0 0.0
Soybeans Insecticide ROTENONE 0.0 0.0 0.0
Soybeans Insecticide SULFOXAFLOR 0.2 0.2 0.0
Soybeans Insecticide TRALOMETHRIN 0.0 0.0 0.0
Soybeans Insecticide CYPERMETHRIN 0.3 0.2 -0.1
Soybeans Insecticide ACETAMIPRID 0.1 0.0 -0.1
Soybeans Insecticide DICROTOPHOS 0.1 0.0 -0.1
Soybeans Insecticide FLUPYRADIFURONE 0.1 0.0 -0.1
Soybeans Insecticide NOVALURON 0.1 0.0 -0.1
Soybeans Insecticide CARBOFURAN 0.4 0.2 -0.2
Soybeans Insecticide DIFLUBENZURON 0.2 0.0 -0.2
Soybeans Insecticide METHOMYL 0.2 0.0 -0.2
Soybeans Insecticide DIMETHOATE 0.7 0.4 -0.3
Soybeans Insecticide METHYL PARATHION 0.7 0.3 -0.4
Soybeans Insecticide MALATHION 1.3 0.9 -0.4
Soybeans Insecticide THIODICARB 0.5 0.0 -0.5
Soybeans Insecticide METHOXYFENOZIDE 0.6 0.0 -0.6
Soybeans Insecticide BIFENAZATE 0.9 0.0 -0.9
Soybeans Insecticide CARBARYL 1.5 0.2 -1.3
Soybeans Insecticide FLUBENDIAMIDE 1.6 0.0 -1.6
Soybeans Insecticide PHORATE 1.7 0.0 -1.7
Soybeans Insecticide ALDICARB 3.1 1.1 -2.0
Soybeans Insecticide CHLORANTRANILIPROLE 2.2 0.0 -2.2
Soybeans Insecticide CHLORPYRIFOS 47.7 45.2 -2.5
Soybeans Insecticide SULFUR 3.0 0.0 -3.0
Soybeans Insecticide CHLOROPICRIN 4.8 0.0 -4.8
Soybeans Insecticide DICHLOROPROPENE 8.4 0.0 -8.4
Soybeans Insecticide ACEPHATE 23.6 2.3 -21.3
Soybeans Insecticide BACILLUS THURINGIENSIS 0.1 NaN NaN
Soybeans Insecticide BIFENTHRIN 7.1 NaN NaN
Soybeans Insecticide HARPIN PROTEIN 0.0 NaN NaN
Soybeans Insecticide POLYHEDROSIS VIRUS 0.1 NaN NaN
Soybeans Insecticide ZETA-CYPERMETHRIN 0.7 NaN NaN